<?php

namespace app\service;

use app\controller\SystemOrganizationalStructure;
use app\model\ClueCommunicateRecord;
use app\model\ClueMobile;
use app\model\CluePerson;
use app\model\CluePersonCertificate;
use app\model\SystemCertificate;
use app\model\UserOrganization;
use app\validate\Organization;
use think\facade\Cache;
use think\facade\Db;

class CluePersonServer
{
    public function spliceWhere($params = [])
    {
        $cluePerson = new CluePerson();
        $person_ids = $cluePerson->getIds();

        $systemCert = new SystemCertificate();
        $cluePersonCert = new CluePersonCertificate();
        $communicationRecord = new ClueCommunicateRecord();
        //是否是重点客户
        if (isset($params['is_zd']) && $params['is_zd']) {
            $list = $cluePerson
                ->where('is_zd', $params['is_zd'])
                ->field('id')
                ->select()
                ->toArray();
            $ids = array_column($list, 'id');
            //两个数组的交集并赋值给person_ids
            $person_ids = $this->handlePersonIds($person_ids, $ids);
        }
        //设置时间
        if ((isset($params['zd_start_date']) && $params['zd_start_date']) && (isset($params['zd_end_date']) && $params['zd_end_date'])) {
            $params['zd_start_date'] = $params['zd_start_date'] . ' 00:00:00';
            $params['zd_end_date'] = $params['zd_end_date'] . ' 23:59:59';
            $list = $cluePerson->where('zd_date', 'between', [$params['zd_start_date'], $params['zd_end_date']])
                ->field('id')
                ->select()
                ->toArray();
            $ids = array_column($list, 'id');
            $person_ids = $this->handlePersonIds($person_ids, $ids);
        }
        //人才姓名
        if (isset($params['person_name']) && $params['person_name']) {
            $list = $cluePerson
                ->where('person_name', 'like', '%' . $params['person_name'] . '%')
                ->field('id')
                ->select()
                ->toArray();

            $ids = array_column($list, 'id');
            //两个数组的交集并赋值给person_ids
            $person_ids = $this->handlePersonIds($person_ids, $ids);
        }

        //身份证号码
        if (isset($params['person_id_card']) && $params['person_id_card']) {
            $list = $cluePerson
                ->where('person_id_card', 'like', '%' . $params['person_id_card'] . '%')
                ->field('id')
                ->select()
                ->toArray();
            $ids = array_column($list, 'id');
            //两个数组的交集并赋值给person_ids
            $person_ids = $this->handlePersonIds($person_ids, $ids);
        }
        //电话
        if (isset($params['mobile']) && $params['mobile']) {
            $modelModel = new ClueMobile();
            $list = $modelModel
                ->where('mobile', '%'.$params['mobile'].'%')
                ->where(['type'=>1])
                ->field('clue_p_id as id')
                ->select()
                ->toArray();

            //每个人才最新的联系电话
            $last_mobile =Db::table('clue_mobile')
                ->where('type',1)
                ->field ('Max(create_date) as create_date,clue_p_id,mobile')
                ->whereNotNull('clue_p_id')
                ->group('clue_p_id')
                ->select()
                ->toArray();
            $ids = [];
            foreach($last_mobile as $item){
                $info = Db::table('clue_mobile')
                    ->where('create_date',$item['create_date'])
                    ->where('clue_p_id',$item['clue_p_id'])
                    ->whereLike('mobile','%'.$params['mobile'].'%')
                    ->find();
                if(!$info){
                    continue;
                }
                $ids[] = $item['clue_p_id'];
            }
            //两个数组的交集并赋值给person_ids
            $person_ids = $this->handlePersonIds($person_ids, $ids);
        }
        //意向结果
        if (isset($params['intention']) && $params['intention']) {
            //每个人才最新的沟通记录
            $last_communicate_record =Db::table('clue_communicate_record')
                ->where('type',1)
                ->field ('Max(communicate_date) as communicate_date,clue_p_id')
                ->whereNotNull('clue_p_id')
                ->group('clue_p_id')
                ->select()
                ->toArray();
            $ids = [];
            foreach($last_communicate_record as $item){
                $info = Db::table('clue_communicate_record')
                    ->where('communicate_date',$item['communicate_date'])
                    ->where('clue_p_id',$item['clue_p_id'])
                    ->where('intention',$params['intention'])
                    ->find();
                if(!$info){
                    continue;
                }
                $ids[] = $item['clue_p_id'];
            }
            //两个数组的交集并赋值给person_ids
            $person_ids = $this->handlePersonIds($person_ids, $ids);
        }


        //呼叫结果
        if (isset($params['call']) && $params['call']) {
            //每个人才最新的沟通记录
            $last_communicate_record =Db::table('clue_communicate_record')
                ->where('type',1)
                ->field ('Max(communicate_date) as communicate_date,clue_p_id')
                ->whereNotNull('clue_p_id')
                ->group('clue_p_id')
                ->select()
                ->toArray();
            $ids = [];
            foreach($last_communicate_record as $item){
                $info = Db::table('clue_communicate_record')
                    ->where('communicate_date',$item['communicate_date'])
                    ->where('clue_p_id',$item['clue_p_id'])
                    ->where('call',$params['call'])
                    ->find();
                if(!$info){
                    continue;
                }
                $ids[] = $item['clue_p_id'];
            }
            //两个数组的交集并赋值给person_ids
            $person_ids = $this->handlePersonIds($person_ids, $ids);
        }


        //证书类别
        if (isset($params['certificate_leibie']) && $params['certificate_leibie']) {
            $list = $cluePerson
                ->where('certificate_leibie', $params['certificate_leibie'])
                ->field('id')
                ->select()
                ->toArray();
            $ids = array_column($list, 'id');
            //两个数组的交集并赋值给person_ids
            $person_ids = $this->handlePersonIds($person_ids, $ids);
        }
        //证书类型
        if (isset($params['certificate_leixing']) && $params['certificate_leixing']) {
            $list = $cluePerson
                ->where('certificate_leixing', $params['certificate_leixing'])
                ->field('id')
                ->select()
                ->toArray();
            $ids = array_column($list, 'id');
            //两个数组的交集并赋值给person_ids
            $person_ids = $this->handlePersonIds($person_ids, $ids);
        }
        //证书等级
        if (isset($params['certificate_dengji']) && $params['certificate_dengji']) {
            $list = $cluePerson
                ->where('certificate_dengji', $params['certificate_dengji'])
                ->field('id')
                ->select()
                ->toArray();
            $ids = array_column($list, 'id');
            //两个数组的交集并赋值给person_ids
            $person_ids = $this->handlePersonIds($person_ids, $ids);
        }
        //证书专业
        if (isset($params['certificate_zhuanye']) && $params['certificate_zhuanye']) {
            $list = $cluePerson
                ->where('certificate_zhuanye', $params['certificate_zhuanye'])
                ->field('id')
                ->select()
                ->toArray();
            $ids = array_column($list, 'id');
            //两个数组的交集并赋值给person_ids
            $person_ids = $this->handlePersonIds($person_ids, $ids);
        }

        //项目
        if (isset($params['project']) && $params['project']) {
            $list = $cluePerson
                ->where('project', $params['project'])
                ->field('id')
                ->select()
                ->toArray();
            $ids = array_column($list, 'id');
            //两个数组的交集并赋值给person_ids
            $person_ids = $this->handlePersonIds($person_ids, $ids);
        }
//        //证书有效期
//        if (isset($params['certificate_period_of_validity']) && $params['certificate_period_of_validity']) {
//            $list = $cluePerson
//                ->where('certificate_period_of_validity', $params['certificate_period_of_validity'])
//                ->field('id')
//                ->select()
//                ->toArray();
//            $ids = array_column($list, 'id');
//            //两个数组的交集并赋值给person_ids
//            $person_ids = $this->handlePersonIds($person_ids, $ids);
//        }
        //证书有效期
        if ((isset($params['certificate_period_of_start_validity']) && $params['certificate_period_of_start_validity']) && (isset($params['certificate_period_of_end_validity']) && $params['certificate_period_of_end_validity'])) {
            $params['certificate_period_of_start_validity'] .= ' 00:00:00';
            $params['certificate_period_of_end_validity'] .= ' 23:59:59';
            $list = $cluePerson->where('certificate_period_of_validity', 'between', [$params['certificate_period_of_start_validity'], $params['certificate_period_of_end_validity']])
                ->field('id')
                ->select()
                ->toArray();
            $ids = array_column($list, 'id');
            $person_ids = $this->handlePersonIds($person_ids, $ids);
        }
        //b证
        if (isset($params['certificate_b']) && ($params['certificate_b'] || $params['certificate_b'] === 0)) {
            $list = $cluePerson
                ->where('certificate_b', $params['certificate_b'])
                ->field('id')
                ->select()
                ->toArray();
            $ids = array_column($list, 'id');
            //两个数组的交集并赋值给person_ids
            $person_ids = $this->handlePersonIds($person_ids, $ids);
        }
//        //b证有效期
//        if (isset($params['certificate_b_period_of_validity']) && $params['certificate_b_period_of_validity']) {
//            $list = $cluePerson
//                ->where('certificate_b_period_of_validity', $params['certificate_b_period_of_validity'])
//                ->field('id')
//                ->select()
//                ->toArray();
//            $ids = array_column($list, 'id');
//            //两个数组的交集并赋值给person_ids
//            $person_ids = $this->handlePersonIds($person_ids, $ids);
//        }

        //B证有效期
        if ((isset($params['certificate_b_period_of_validity_start']) && $params['certificate_b_period_of_validity_start']) && (isset($params['certificate_b_period_of_validity_end']) && $params['certificate_b_period_of_validity_end'])) {
            $params['certificate_b_period_of_validity_start'] = $params['certificate_b_period_of_validity_start'] . ' 00:00:00';
            $params['certificate_b_period_of_validity_end'] = $params['certificate_b_period_of_validity_end'] . ' 23:59:59';
            $list = $cluePerson->where('certificate_b_period_of_validity', 'between', [$params['certificate_b_period_of_validity_start'], $params['certificate_b_period_of_validity_end']])
                ->field('id')
                ->select()
                ->toArray();
            $ids = array_column($list, 'id');
            $person_ids = $this->handlePersonIds($person_ids, $ids);
        }
        //社保
        if (isset($params['social_security']) && ($params['social_security'] || $params['social_security'] === 0)) {
            $list = $cluePerson
                ->where('social_security', $params['social_security'])
                ->field('id')
                ->select()
                ->toArray();
            $ids = array_column($list, 'id');
            //两个数组的交集并赋值给person_ids
            $person_ids = $this->handlePersonIds($person_ids, $ids);
        }
        //其它证书
        if (isset($params['other_certificate']) && $params['other_certificate']) {
            $list = $systemCert
                ->where('certificate_name', 'like', '%' . $params['other_certificate'] . '%')
                ->field('id')
                ->select()
                ->toArray();
            $ids = array_column($list, 'id');
            $clue_person_cert_list = $cluePersonCert
                ->where('certificate_id', 'in', $ids)
                ->field('clue_p_id')
                ->select()
                ->toArray();
            $ids = array_column($clue_person_cert_list, 'clue_p_id');
            //两个数组的交集并赋值给person_ids
            $person_ids = $this->handlePersonIds($person_ids, $ids);
        }
        //创建时间
        if ((isset($params['create_start_date']) && $params['create_start_date']) && (isset($params['create_end_date']) && $params['create_end_date'])) {
            $params['create_start_date'] = $params['create_start_date'] . ' 00:00:00';
            $params['create_end_date'] = $params['create_end_date'] . ' 23:59:59';
            $list = $cluePerson->where('create_date', 'between', [$params['create_start_date'], $params['create_end_date']])
                ->field('id')
                ->select()
                ->toArray();
            $ids = array_column($list, 'id');
            $person_ids = $this->handlePersonIds($person_ids, $ids);
        }
        //分配时间
        if ((isset($params['allocation_start_date']) && $params['allocation_start_date']) && (isset($params['allocation_end_date']) && $params['allocation_end_date'])) {
            $params['allocation_start_date'] = $params['allocation_start_date'] . ' 00:00:00';
            $params['allocation_end_date'] = $params['allocation_end_date'] . ' 23:59:59';
            $list = $cluePerson->where('allocation_date', 'between', [$params['allocation_start_date'], $params['allocation_end_date']])
                ->field('id')
                ->select()
                ->toArray();
            $ids = array_column($list, 'id');
            $person_ids = $this->handlePersonIds($person_ids, $ids);
        }
        //沟通时间
        if ((isset($params['communication_start_date']) && $params['communication_start_date']) && (isset($params['communication_end_date']) && $params['communication_end_date'])) {
            $params['communication_start_date'] .= ' 00:00:00';
            $params['communication_end_date'] .= ' 23:59:59';
            $communicationRecord = new ClueCommunicateRecord();
            $list = $communicationRecord->where('communicate_date', 'between', [$params['communication_start_date'], $params['communication_end_date']])
                ->where('type', 1)
                ->field('clue_p_id')
                ->select()
                ->toArray();
            $ids = array_column($list, 'clue_p_id');
            $person_ids = $this->handlePersonIds($person_ids, $ids);
        }
        //下次沟通时间
        if ((isset($params['next_start_communicate_date']) && $params['next_start_communicate_date']) && (isset($params['next_end_communicate_date']) && $params['next_end_communicate_date'])) {
            $params['next_start_communicate_date'] = $params['next_start_communicate_date'] . ' 00:00:00';
            $params['next_end_communicate_date'] = $params['next_end_communicate_date'] . ' 23:59:59';
            $list = $communicationRecord->where('next_communicate_date', 'between', [$params['next_start_communicate_date'], $params['next_end_communicate_date']])
                ->where('type', 1)
                ->field('clue_p_id')
                ->select()
                ->toArray();
            $ids = array_column($list, 'clue_p_id');
            $person_ids = $this->handlePersonIds($person_ids, $ids);
        }
        //沟通记录
        if (isset($params['communicate_content']) && $params['communicate_content']) {
            $list = $communicationRecord
                ->where('communicate_content', 'like', '%' . $params['communicate_content'] . '%')
                ->where('type', 1)
                ->field('clue_p_id')
                ->select()
                ->toArray();
            $ids = array_column($list, 'clue_p_id');
            //两个数组的交集并赋值给person_ids
            $person_ids = $this->handlePersonIds($person_ids, $ids);
        }
//        //证书注销时间
//        if (isset($params['certificate_log_off']) && $params['certificate_log_off']) {
//            $start_time = $params['certificate_log_off'] . ' 00:00:00';
//            $end_time = $params['certificate_log_off'] . ' 23:59:59';
//            $list = $cluePerson
//                ->where('certificate_log_off', 'between', [$start_time, $end_time])
//                ->field('id')
//                ->select()
//                ->toArray();
//            $ids = array_column($list, 'id');
//            //两个数组的交集并赋值给person_ids
//            $person_ids = $this->handlePersonIds($person_ids, $ids);
//        }
        //证书注销时间
        if ((isset($params['certificate_log_off_start']) && $params['certificate_log_off_start']) && (isset($params['certificate_log_off_end']) && $params['certificate_log_off_end'])) {
            $params['certificate_log_off_start'] = $params['certificate_log_off_start'] . ' 00:00:00';
            $params['certificate_log_off_end'] = $params['certificate_log_off_end'] . ' 23:59:59';
            $list = $cluePerson->where('certificate_log_off', 'between', [$params['certificate_log_off_start'], $params['certificate_log_off_end']])
//                ->where('type', 1)
                ->field('id')
                ->select()
                ->toArray();
            $ids = array_column($list, 'id');
            $person_ids = $this->handlePersonIds($person_ids, $ids);
        }
        //是否添加企业微信
        if (isset($params['is_wx']) && $params['is_wx']) {
            $list = $cluePerson
                ->where('is_wx', $params['is_wx'])
                ->field('id')
                ->select()
                ->toArray();
            $ids = array_column($list, 'id');
            //两个数组的交集并赋值给person_ids
            $person_ids = $this->handlePersonIds($person_ids, $ids);
        }
        //他人合作到期时间
        if ((isset($params['become_start_due']) && $params['become_start_due']) && (isset($params['become_end_due']) && $params['become_end_due'])) {
            $start_time = $params['become_start_due'] . ' 00:00:00';
            $end_time = $params['become_end_due'] . ' 23:59:59';
            $list = $cluePerson
                ->where('become_due', 'between', [$start_time, $end_time])
                ->field('id')
                ->select()
                ->toArray();
            $ids = array_column($list, 'id');
            //两个数组的交集并赋值给person_ids
            $person_ids = $this->handlePersonIds($person_ids, $ids);
        }
        //备注
        if (isset($params['notes']) && $params['notes']) {
            $list = $cluePerson
                ->where('notes', 'like', '%' . $params['notes'] . '%')
                ->field('id')
                ->select()
                ->toArray();
            $ids = array_column($list, 'id');
            //两个数组的交集并赋值给person_ids
            $person_ids = $this->handlePersonIds($person_ids, $ids);
        }
        //负责人 修改
        if (isset($params['f_uid']) && $params['f_uid']) {
            $userModel = new \app\model\User();
            //模糊查询用户id
            $userId = $userModel
                ->whereLike ('nickname','%'.$params['f_uid'].'%')
                ->column ('id');

            $list = $cluePerson
                ->where('f_uid', 'in', $userId)
                ->field('id')
                ->select()
                ->toArray();
            $ids = array_column($list, 'id');
            //两个数组的交集并赋值给person_ids
            $person_ids = $this->handlePersonIds($person_ids, $ids);
        }
        //订单状态
        if (isset($params['status']) && $params['status']) {
            $list = $cluePerson
                ->where('status', $params['status'])
                ->field('id')
                ->select()
                ->toArray();
            $ids = array_column($list, 'id');
            //两个数组的交集并赋值给person_ids
            $person_ids = $this->handlePersonIds($person_ids, $ids);
        }
        //是否分配
        if (isset($params['is_f']) && $params['is_f']) {
            $list = $cluePerson
                ->where('is_f', $params['is_f'])
                ->field('id')
                ->select()
                ->toArray();
            $ids = array_column($list, 'id');
            //两个数组的交集并赋值给person_ids
            $person_ids = $this->handlePersonIds($person_ids, $ids);
        }

        //人员查询
        if (isset($params['uid']) && $params['uid']){

            $ids = $cluePerson
                ->where('f_uid', $params['uid'])
                ->column ('id');

            //两个数组的交集并赋值给person_ids
            $person_ids = $this->handlePersonIds($person_ids, $ids);
        }

        //部门查询
        if (isset($params['org_id']) && $params['org_id']){
            //查询该组织下所有部门&& 所有人员
            $systemService = new System();
            $orgModel = new UserOrganization();
            $userModel = new \app\model\User();
            $orgStr = $systemService->getOrgIdStr($params['org_id'],$orgModel->getStructure ());
            $uidArr = $userModel->getDepartmentIdsUserIds ($orgStr);

            $ids = $cluePerson
                ->whereIn('f_uid', $uidArr)
                ->column ('id');

            //两个数组的交集并赋值给person_ids
            $person_ids = $this->handlePersonIds($person_ids, $ids);
        }

        $person_ids = !$person_ids ? [0] : $person_ids;
        return $person_ids;

    }

    public function handlePersonIds($person_ids, $other_ids)
    {
        if (!$person_ids || !$other_ids) {
            return [];
        }
        if ($person_ids && $other_ids) {
            $person_ids = array_intersect($person_ids, $other_ids);
        } elseif ($person_ids && !$other_ids) {
            $person_ids = [];
        } elseif (!$person_ids && $other_ids) {
            $person_ids = $other_ids;
        } elseif (!$person_ids && !$other_ids) {
            $person_ids = [];
        }
        return $person_ids;
    }
}